package com.junzhiit.nengji.shop.dao.impl;

import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import com.bainaeco.framework.base.dao.AbstractBaseDAO;
import com.bainaeco.framework.common.jdbc.StatementParameter;
import com.bainaeco.framework.enums.IsDelete;
import com.junzhiit.nengji.shop.bo.AdvertiseBO;
import com.junzhiit.nengji.shop.bo.ModulesAdvertiseBO;
import com.junzhiit.nengji.shop.dao.ModulesAdvertiseDAO;
import com.junzhiit.nengji.shop.model.ModulesAdvertise;
import com.junzhiit.nengji.shop.model.enums.Is;

@Repository
public class ModulesAdvertiseDAOImpl extends AbstractBaseDAO<ModulesAdvertise> implements ModulesAdvertiseDAO {

	@Override
	public List<AdvertiseBO> findAll(String modulesId, int pageNumber, int maxPageRow) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select a.*,md.id modules_advertise_id,t.name advertise_type_name");
		sql.append(" from modules_advertise md");
		sql.append(" left join advertise a on md.advertise_id=a.id");
		sql.append(" left join `advertise_type` t on a.advertise_type_id=t.id");
		sql.append(" where md.is_delete=? and a.is_delete=? and md.modules_id=?");
		sql.append(" order by sort desc");

		StatementParameter param = new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(modulesId);

		return super.queryForPagingList(sql.toString(), AdvertiseBO.class, param, pageNumber, maxPageRow);
	}

	@Override
	public int getTotalCount(String modulesId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) from modules_advertise md");
		sql.append(" left join advertise a on md.advertise_id=a.id");
		sql.append(" where md.is_delete=? and a.is_delete=? and md.modules_id=?");

		StatementParameter param = new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(modulesId);

		return queryForInt(sql.toString(), param);
	}

	@Override
	public List<ModulesAdvertiseBO> findModulesAdvertise(String columnCode,String shopId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select ma.modules_id,m.name 'modules_name',ma.id,m.code 'modules_code',ma.advertise_id,a.img_url,a.advertise_type_id,a.status,");
		sql.append("a.advertise_url,at.name 'advertise_type_name',c.id 'column_id',c.code 'column_code',c.name 'column_name',m.is_recommand_goods,m.goods_quantity");
		sql.append(" from modules_advertise ma");
		sql.append(" inner join modules m on ma.modules_id=m.id");
		sql.append(" inner join `column` c on c.id=m.column_id");
		sql.append(" inner join advertise a on ma.advertise_id=a.id");
		sql.append(" inner join advertise_type at on a.advertise_type_id=at.id");
		sql.append(" where ma.is_delete=? and a.status=? and a.is_delete= ? ");
		
		StatementParameter param = new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());

		if(StringUtils.isNoneBlank(columnCode)){
			sql.append(" and c.code=?");
			param.setString(columnCode);
		}
		
		if(StringUtils.isNotBlank(shopId)){
			sql.append(" and c.shop_id = ? ");
			param.setString(shopId);
		}else{
			sql.append(" and c.shop_id is null ");
		}
		
		sql.append(" order by m.sort asc,a.sort asc");
		return queryForList(sql.toString(), ModulesAdvertiseBO.class, param);
	}
	
	@Override
	public List<ModulesAdvertiseBO> findModulesAdvertiseByModulesCode(String modulesCode,String shopId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select ma.modules_id,m.name 'modules_name',ma.id,m.code 'modules_code',ma.advertise_id,a.img_url,a.advertise_type_id,a.status,");
		sql.append("a.advertise_url,at.name 'advertise_type_name',c.id 'column_id',c.code 'column_code',c.name 'column_name',m.is_recommand_goods,m.goods_quantity");
		sql.append(" from modules_advertise ma");
		sql.append(" inner join modules m on ma.modules_id=m.id");
		sql.append(" inner join `column` c on c.id=m.column_id");
		sql.append(" inner join advertise a on ma.advertise_id=a.id");
		sql.append(" inner join advertise_type at on a.advertise_type_id=at.id");
		sql.append(" where ma.is_delete=? and a.status=? and a.is_delete= ? ");
		
		StatementParameter param = new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());

		if(StringUtils.isNoneBlank(modulesCode)){
			sql.append(" and m.code=?");
			param.setString(modulesCode);
		}
		
		if(StringUtils.isNotBlank(shopId)){
			sql.append(" and c.shop_id = ? ");
			param.setString(shopId);
		}else{
			sql.append(" and c.shop_id is null ");
		}
		
		sql.append(" order by m.sort asc,a.sort asc");
		return queryForList(sql.toString(), ModulesAdvertiseBO.class, param);
	}
	
	@Override
	public List<ModulesAdvertiseBO> findByColumnCode(String columnCode) {
		StatementParameter param = new StatementParameter();
		StringBuffer sql = new StringBuffer();
		
		sql.append("SELECT ma.modules_sort, ma.modules_name, ma.modules_code, a.* ");
		sql.append("FROM ( ");
		sql.append("	SELECT m.modules_name, m.modules_sort, m.modules_code, ma.* ");
		sql.append("	FROM ( ");
		sql.append("		SELECT m.id, m.name modules_name, m.sort modules_sort, m.code modules_code ");
		sql.append("		FROM ( ");
		sql.append("			SELECT id, `name` ");
		sql.append("			FROM `column` ");
		sql.append("			WHERE code = ? ");
		sql.append("			AND IFNULL(shop_id, '') = '' ");
		sql.append("		) c ");
		sql.append("		JOIN modules m ");
		sql.append("		ON c.id = m.column_id ");
		sql.append("		AND m.is_delete = ? ");
		sql.append("	) m  ");
		sql.append("	JOIN modules_advertise ma ");
		sql.append("	ON m.id = ma.modules_id ");
		sql.append("	WHERE ma.is_delete = 0 ");
		sql.append(") ma ");
		sql.append("JOIN advertise a ");
		sql.append("ON ma.advertise_id = a.id ");
		sql.append("AND a.status = ? ");
		sql.append("AND a.is_delete = ? ");
		sql.append("ORDER BY ma.modules_sort ASC, a.sort ASC ");
		
		param.setString(columnCode);
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(Is.NO.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());
		
		return queryForList(sql.toString(), ModulesAdvertiseBO.class, param);
	}
}
